package ldh.database.util;

import ldh.database.*;
import ldh.maker.util.FreeMakerUtil;

import java.sql.*;
import java.util.*;
import java.util.Map.Entry;

public class MetaUtil {

//	private static final Logger logger = Logger.getLogger(MetaUtil.class);
	
	public static List<String[]> getAllTable(Connection connection, String tableOwer, String dbName) {
		List<String[]> tableNames = new ArrayList<String[]>();
		ResultSet tableRet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			tableRet = dbMeta.getTables(dbName, null, "%", new String[]{"TABLE"});
			while(tableRet.next()) {
				String talbeName = tableRet.getString("TABLE_NAME");
				String talbeComment = tableRet.getString("REMARKS");
				String tableRole = tableRet.getString(2);
				String[] tables = new String[]{talbeName, talbeComment};
				if (tableOwer != null) {
					if (tableOwer.equalsIgnoreCase(tableRole))
						tableNames.add(tables);
				} else {
					tableNames.add(tables);
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, tableRet);
		}
		if (FreeMakerUtil.isMysql()) {
			for (String[] tn : tableNames) {
				String comment = getTableComment(connection, tn[0]);
				tn[1] = comment;
			}
		}
		return tableNames;
	}
	
	public static List<String> getAllSequence(Connection connection, String tableOwer, String dbName) {
		List<String> tableNames = new ArrayList<String>();
		ResultSet tableRet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			tableRet = dbMeta.getTables(dbName, null, "%", new String[]{"SEQUENCE"});
			while(tableRet.next()) {
				String sequenceName = tableRet.getString(3);
				String sequenceRole = tableRet.getString(2);
				if (tableOwer != null) {
					if (tableOwer.equalsIgnoreCase(sequenceRole))
						tableNames.add(sequenceName);
				} else {
					tableNames.add(sequenceName);
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, tableRet);
		}
		return tableNames;
	}
	
	public static Table getTable(Connection connection, String schemaName, String db, String tableName) {
		List<Column> columns = getColumns(connection, db, schemaName, tableName);
		PrimaryKey primaryKey = getPrimaryKey(connection, columns, db, schemaName, tableName);
		Set<ForeignKey> foreignKey = getForeignKey(connection, columns, db, schemaName, tableName);
		Set<UniqueIndex> indexies = getUniqueIndexies(connection, db, schemaName, tableName);
		Table table = new Table(tableName, columns, primaryKey, foreignKey);
		if (indexies != null && indexies.size() > 0) {
			for (UniqueIndex ui : indexies) {
				List<Column> columnIndexies = new ArrayList<Column>();
				for (String tt : ui.getColumnNames()) {
					for (Column c : columns) {
						if (tt.equals(c.getName())) {
							columnIndexies.add(c);
						}
					}
				}
				ui.setColumns(columnIndexies);
			}
			table.setIndexies(indexies);
		}
		
		return table;
	}
	
	public static List<Column> getColumns(Connection connection, String db, String schemaName, String tableName) {
		List<Column> columns = new ArrayList<Column>();
		ResultSet columnRet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			columnRet = dbMeta.getColumns(db, schemaName, tableName, "%");
			while(columnRet.next()) {
				String columnName = columnRet.getString("COLUMN_NAME");
				String columnType = columnRet.getString("TYPE_NAME");
				String columnComment = columnRet.getString("REMARKS");
				int columnSize = columnRet.getInt("COLUMN_SIZE");
				int columnNullable = columnRet.getInt("NULLABLE");
				int columnScale  = columnRet.getInt("DECIMAL_DIGITS");
				int type = columnRet.getInt(5);
				int datasize = columnRet.getInt("COLUMN_SIZE");
				int digits = columnRet.getInt("DECIMAL_DIGITS");
				int nullable = columnRet.getInt("NULLABLE");
				JdbcType jt = JdbcType.forCode(type);

				if (type == 3) {
					if (datasize == 11 && digits == 0) {
						jt = JdbcType.forCode(Types.BIGINT);
					}
					
				}
				Column column = new Column(columnName, columnComment, jt.name());
				column.setSize(columnSize);
				column.setNullable(columnNullable > 0);
				column.setScale(columnScale);
				columns.add(column);

			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, columnRet);
		}
		return columns;
	}
	
	public static PrimaryKey getPrimaryKey(Connection connection, List<Column> columns, String db,
										   String schemaName, String tableName) {
		Set<String> columnNames = new HashSet<String>();
		ResultSet pkRSet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			pkRSet = dbMeta.getPrimaryKeys(db, schemaName, tableName);
			while(pkRSet.next()) {
				String columnName = pkRSet.getString("COLUMN_NAME");
				columnNames.add(columnName);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, pkRSet);
		}
		if (columnNames == null || columnNames.size() < 1) {
			return null;
		}
		PrimaryKey primaryKey = new PrimaryKey(columns, columnNames);
		return primaryKey;
	}
	
	public static Set<ForeignKey> getForeignKey(Connection connection, List<Column> allColumn, String db, String schemaName, String tableName) {
		Set<ForeignKey> foreignKeys = new HashSet<ForeignKey>();
		ResultSet pkRSet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			pkRSet = dbMeta.getImportedKeys(db, schemaName, tableName);
			while(pkRSet.next()) {
				String tName = pkRSet.getString(3);
				String keyName = pkRSet.getString(4);
				String columnName = pkRSet.getString(8);

//				String pkTableName = pkRSet.getString("PKTABLE_NAME");
//				String pkColumnName = pkRSet.getString("PKCOLUMN_NAME");
//				String fkTableName = pkRSet.getString("FKTABLE_NAME");
//				String fkColumnName = pkRSet.getString("FKCOLUMN_NAME");
//				String fkName = pkRSet.getString("FK_NAME");

				ForeignKey foreignKey = new ForeignKey(tableName, columnName, keyName,  tName);
				if (foreignKeys.contains(foreignKey)) {
					foreignKey.handle(allColumn);
					foreignKeys.add(foreignKey);
				}

			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, pkRSet);
		}
		return foreignKeys;
	}
	
	public static Set<UniqueIndex> getUniqueIndexies(Connection connection, String db, String schemaName, String tableName) {
		Set<UniqueIndex> indexs = new HashSet<UniqueIndex>();
		ResultSet pkRSet = null;
		try {
			DatabaseMetaData dbMeta = connection.getMetaData();
			pkRSet = dbMeta.getIndexInfo(db, schemaName, tableName, true, false);
			Map<String, List<String>> data = new HashMap<String, List<String>>();
			while(pkRSet.next()) {
				String columnName = pkRSet.getString("COLUMN_NAME");
				String indexName = pkRSet.getString("INDEX_NAME");
				if (columnName != null) {
					if (!data.containsKey(indexName)) {
						data.put(indexName, new ArrayList<String>());
					}
					data.get(indexName).add(columnName);
				}
			}
			for (Entry<String, List<String>> dd : data.entrySet()) {
				UniqueIndex ui = new UniqueIndex(dd.getKey(), dd.getValue());
				indexs.add(ui);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionUtil.close(null, pkRSet);
		}
		return indexs;
	}
	
	//获取mysql的表类型
	private static String getTableComment(Connection connection, String tableName){
		Statement st=null;
		ResultSet rs=null;
		String result="";
		try {
		    st = connection.createStatement();
		    rs = st.executeQuery("SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='"+tableName+"'");
		    while(rs.next()){
		    	result = rs.getString(1);
		    	break;
		    }
		 } catch (Exception e) {
			 e.printStackTrace();
		 } finally {
			 ConnectionUtil.close(null, st, rs);
		 }
		 return result;
	}
	
	public static void main(String[] args) {

		
	}
}
